package cn.sylinx.horm.core;

import cn.sylinx.horm.core.common.OrmUtil;
import cn.sylinx.horm.core.common.Page;
import cn.sylinx.horm.core.common.PreparedCallable;
import cn.sylinx.horm.core.common.Record;
import cn.sylinx.horm.dialect.Dialect;
import cn.sylinx.horm.dialect.fs.FS;
import cn.sylinx.horm.dialect.fs.PlainFS;
import cn.sylinx.horm.exception.HORMException;
import cn.sylinx.horm.model.base.BaseModel;
import cn.sylinx.horm.model.cache.ModelCacheUtil;
import cn.sylinx.horm.model.cache.ModelFabric;
import cn.sylinx.horm.resource.ClasspathSqlResource;
import cn.sylinx.horm.resource.parse.DefaultSqlParser;
import cn.sylinx.horm.resource.parse.SqlParser;
import cn.sylinx.horm.transaction.jdbc.TransactionSupport;
import cn.sylinx.horm.type.handler.TypeHandler;
import cn.sylinx.horm.util.DbKit;
import cn.sylinx.horm.util.ExceptionCatcher;
import cn.sylinx.horm.util.GLog;
import cn.sylinx.horm.util.Pair;
import cn.sylinx.horm.util.Tuple;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * orm support
 *
 * @author johnhan
 */
public class OrmClient extends AbstractSqlClient {

    private Dialect dialect;

    // 默认 SqlParser
    private SqlParser sqlParser = new DefaultSqlParser();

    public Dialect getDialect() {
        return dialect;
    }

    @Override
    public TransactionSupport getTransactionSupport() {
        throw new UnsupportedOperationException();
    }

    public void setDialect(Dialect dialect) {
        this.dialect = dialect;
    }

    public SqlParser getSqlParser() {
        return sqlParser;
    }

    public void setSqlParser(SqlParser sqlParser) {
        this.sqlParser = sqlParser;
    }

    @Override
    public <T, R> List<R> queryForSingleColumn(FS<T> fluentSql) {
        Tuple kv = getDialect().getSqlBuilder().buildSelectSQL(fluentSql);
        String sql = kv.getObject(0);
        Object[] params = kv.getObject(1);
        Class<R> modelClass = kv.getObject(2);
        return queryListForSingleColumn(sql, params, modelClass);
    }

    @Override
    public <T, R> R queryFirstForSingleColumn(FS<T> fluentSql) {
        List<R> objectList = queryForSingleColumn(fluentSql);
        return objectList != null && !objectList.isEmpty() ? objectList.get(0) : null;
    }

    @Override
    public <T> T queryFirst(FS<T> fluentSql) {
        List<T> dataList = query(fluentSql);
        return dataList != null && !dataList.isEmpty() ? dataList.get(0) : null;
    }

    @Override
    public int count(FS<?> fluentSql) {
        Pair kv = getDialect().getSqlBuilder().buildCountSQL(fluentSql);
        String sql = kv.getFirst();
        Object[] params = kv.getSecond();
        Record r = queryRecord(sql, params);
        return r == null ? 0 : Integer.parseInt(r.get(1).toString());
    }

    @Override
    public <T> Page<T> queryPage(FS<T> fluentSql, int pageNumber, int pageSize) {
        Tuple t = getDialect().getSqlBuilder().buildSelectSQL(fluentSql);
        String sql = t.getObject(0);
        Object[] params = t.getObject(1);
        Class<T> modelClass = t.getObject(2);
        return queryPage(sql, pageNumber, pageSize, params, modelClass);
    }

    @Override
    public Page<Record> queryPage(PlainFS fluentSql, int pageNumber, int pageSize) {
        Tuple t = getDialect().getSqlBuilder().buildSelectSQL(fluentSql);
        String sql = t.getObject(0);
        Object[] params = t.getObject(1);
        return queryPage(sql, pageNumber, pageSize, params);
    }

    @Override
    public <T> List<T> query(FS<T> fluentSql) {
        Tuple kv = getDialect().getSqlBuilder().buildSelectSQL(fluentSql);
        String sql = kv.getObject(0);
        Object[] params = kv.getObject(1);
        Class<T> modelClass = kv.getObject(2);
        return queryList(sql, params, modelClass);
    }

    @Override
    public List<Record> query(PlainFS fluentSql) {
        Tuple kv = getDialect().getSqlBuilder().buildSelectSQL(fluentSql);
        String sql = kv.getObject(0);
        Object[] params = kv.getObject(1);
        return queryRecords(sql, params);
    }

    @Override
    public Record queryFirst(PlainFS fluentSql) {
        Tuple kv = getDialect().getSqlBuilder().buildSelectSQL(fluentSql);
        String sql = kv.getObject(0);
        Object[] params = kv.getObject(1);
        return queryRecord(sql, params);
    }

    @Override
    public int update(PlainFS fluentSql) {
        Tuple kv = getDialect().getSqlBuilder().buildUpdateSQL(fluentSql);
        String sql = kv.getObject(0);
        Object[] params = kv.getObject(1);
        return ExceptionCatcher.call(() -> update(sql, params));
    }

    @Override
    public int delete(PlainFS fluentSql) {
        Pair kv = getDialect().getSqlBuilder().buildDeleteSQL(fluentSql);
        String sql = kv.getFirst();
        Object[] params = kv.getSecond();
        return ExceptionCatcher.call(() -> delete(sql, params));
    }

    @Override
    public int count(PlainFS fluentSql) {
        Pair kv = getDialect().getSqlBuilder().buildCountSQL(fluentSql);
        String sql = kv.getFirst();
        Object[] params = kv.getSecond();
        Record r = queryRecord(sql, params);
        return r == null ? 0 : Integer.parseInt(r.get(1).toString());
    }

    @Override
    public Serializable insert(PlainFS fluentSql) {
        Pair kv = getDialect().getSqlBuilder().buildInsertSQL(fluentSql);
        String sql = kv.getFirst();
        Object[] params = kv.getSecond();
        try {
            Object obj = insertForRetrieval(sql, params);
            if (obj == null) {
                return null;
            }
            if (obj instanceof Serializable) {
                return (Serializable) obj;
            }
            return obj.toString();
        } catch (Exception e) {
            GLog.error("OrmClient.save fs error", e);
            throw new HORMException(e);
        }
    }

    @Override
    public int update(FS<?> fluentSql) {
        Pair kv = getDialect().getSqlBuilder().buildUpdateSQL(fluentSql);
        String sql = kv.getFirst();
        Object[] params = kv.getSecond();
        return ExceptionCatcher.call(() -> update(sql, params));
    }

    @Override
    public int delete(FS<?> fluentSql) {
        Pair kv = getDialect().getSqlBuilder().buildDeleteSQL(fluentSql);
        String sql = kv.getFirst();
        Object[] params = kv.getSecond();
        return ExceptionCatcher.call(() -> delete(sql, params));
    }

    @Override
    public Serializable insert(FS<?> fluentSql) {
        Pair kv = getDialect().getSqlBuilder().buildInsertSQL(fluentSql);
        String sql = kv.getFirst();
        Object[] params = kv.getSecond();
        try {
            Object obj = insertForRetrieval(sql, params);
            if (obj == null) {
                return null;
            }
            if (obj instanceof Serializable) {
                return (Serializable) obj;
            }
            return obj.toString();
        } catch (Exception e) {
            GLog.error("OrmClient.save fs error", e);
            throw new HORMException(e);
        }
    }

    @Override
    public <T> int[] insertBatch(List<T> t) {

        Pair pair = getDialect().getSqlBuilder().buildBatchInsertSQL(t);
        String sql = pair.getObject(0);
        List<Object[]> params = pair.getObject(1);

        try {
            return insertBatch(sql, params);
        } catch (Exception e) {
            GLog.error("OrmClient.insertBatch error", e);
            throw new HORMException(e);
        }
    }

    @Override
    public <T> int insert(T t) {

        if (t instanceof BaseModel) {
            if (((BaseModel) t).getGmtCreate() == null) {
                ((BaseModel) t).setGmtCreate(new Date());
            }
        }

        Pair pair = getDialect().getSqlBuilder().buildInsertSQL(t);
        String sql = pair.getObject(0);
        Object[] params = pair.getObject(1);
        try {
            return insert(sql, params);
        } catch (Exception e) {
            GLog.error("OrmClient.insert error", e);
            throw new HORMException(e);
        }
    }

    public <T> Serializable save(T t) {
        if (t instanceof BaseModel) {
            if (((BaseModel) t).getGmtCreate() == null) {
                ((BaseModel) t).setGmtCreate(new Date());
            }
        }
        Pair pair = getDialect().getSqlBuilder().buildInsertSQL(t);
        String sql = pair.getObject(0);
        Object[] params = pair.getObject(1);
        try {
            Object obj = insertForRetrieval(sql, params);
            if (obj == null) {
                return null;
            }
            if (obj instanceof Serializable) {
                return (Serializable) obj;
            }
            return obj.toString();
        } catch (Exception e) {
            GLog.error("OrmClient.save error", e);
            throw new HORMException(e);
        }
    }

    public <T> int delete(T t) {
        Pair pair = getDialect().getSqlBuilder().buildDeleteSQL(t);
        String sql = pair.getObject(0);
        Object[] params = pair.getObject(1);
        try {
            return delete(sql, params);
        } catch (Exception e) {
            GLog.error("OrmClient.delete error", e);
            throw new HORMException(e);
        }
    }

    public <T> int update(T t) {
        if (t instanceof BaseModel) {
            if (((BaseModel) t).getGmtModify() == null) {
                ((BaseModel) t).setGmtModify(new Date());
            }
        }
        Pair pair = getDialect().getSqlBuilder().buildUpdateSQL(t);
        String sql = pair.getObject(0);
        Object[] params = pair.getObject(1);
        try {
            return update(sql, params);
        } catch (Exception e) {
            GLog.error("OrmClient.update error", e);
            throw new HORMException(e);
        }
    }

    public int update(ClasspathSqlResource sqlResource, Map<String, Object> params) {
        Pair tp = parseSql(sqlResource, params);
        String sql = (String) tp.get(0);
        Object[] paramsArray = (Object[]) tp.get(1);
        return ExceptionCatcher.call(() -> update(sql, paramsArray));
    }

    @Override
    public Serializable insertForRetrieval(ClasspathSqlResource sqlResource, Map<String, Object> params) {
        Pair tp = parseSql(sqlResource, params);
        String sql = (String) tp.get(0);
        Object[] paramsArray = (Object[]) tp.get(1);

        return ExceptionCatcher.call(() -> {
            Object pk = insertForRetrieval(sql, paramsArray);
            if (pk == null) {
                return null;
            }

            return pk.toString();
        });
    }

    public boolean execute(ClasspathSqlResource sqlResource, Map<String, Object> params) {
        Pair tp = parseSql(sqlResource, params);
        String sql = (String) tp.get(0);
        Object[] paramsArray = (Object[]) tp.get(1);
        return ExceptionCatcher.call(() -> execute(sql, paramsArray));
    }

    public <T> T get(Serializable id, Class<T> modelClass) {
        Pair pair = getDialect().getSqlBuilder().buildQueryByFieldSQL(Collections.singletonList(Pair.apply("id", id)), modelClass);
        String sql = pair.getObject(0);
        Object[] params = pair.getObject(1);
        List<T> dataList = queryList(sql, params, modelClass);
        if (dataList != null && !dataList.isEmpty()) {
            return dataList.get(0);
        }
        return null;
    }

    public Page<Record> queryPage(String sql, int pageNumber, int pageSize, Object[] params) {
        Tuple t = buildPaginatorSql(sql, pageNumber, pageSize, params);
        int totalRow = t.getObject(0);
        if (totalRow == 0) {
            // 空
            Page<Record> emptyPage = new Page<>();
            emptyPage.setPageSize(pageSize);
            return emptyPage;
        }
        int totalPage = t.getObject(1);
        String sqlLimit = t.getObject(2);
        Object[] finalParams = t.getObject(3);
        List<Record> dataList = queryRecords(sqlLimit, finalParams);
        return new Page<>(dataList, pageNumber, pageSize, totalPage, totalRow);
    }

    public Page<Record> queryPage(ClasspathSqlResource sqlResource, int pageNumber, int pageSize,
                                  Map<String, Object> params) {
        Pair tp = parseSql(sqlResource, params);
        String sql = (String) tp.get(0);
        Object[] paramsArray = (Object[]) tp.get(1);
        return queryPage(sql, pageNumber, pageSize, paramsArray);
    }

    public <T> Page<T> queryPage(String sql, int pageNumber, int pageSize, Object[] params, Class<T> clz) {
        Tuple t = buildPaginatorSql(sql, pageNumber, pageSize, params);
        int totalRow = t.getObject(0);
        if (totalRow == 0) {
            // 空
            Page<T> emptyPage = new Page<>();
            emptyPage.setPageSize(pageSize);
            return emptyPage;
        }
        int totalPage = t.getObject(1);
        String sqlLimit = t.getObject(2);
        Object[] finalParams = t.getObject(3);
        List<T> dataList = queryList(sqlLimit, finalParams, clz);
        return new Page<T>(dataList, pageNumber, pageSize, totalPage, totalRow);
    }

    public <T> Page<T> queryPage(ClasspathSqlResource sqlResource, int pageNumber, int pageSize,
                                 Map<String, Object> params, Class<T> clz) {
        Pair tp = parseSql(sqlResource, params);
        String sql = (String) tp.get(0);
        Object[] paramsArray = (Object[]) tp.get(1);
        return queryPage(sql, pageNumber, pageSize, paramsArray, clz);
    }

    public <R> Page<R> queryPageForSingleColumn(String sql, int pageNumber, int pageSize, Object[] params,
                                                Class<R> clz) {
        Tuple t = buildPaginatorSql(sql, pageNumber, pageSize, params);
        int totalRow = t.getObject(0);
        if (totalRow == 0) {
            // 空
            Page<R> emptyPage = new Page<>();
            emptyPage.setPageSize(pageSize);
            return emptyPage;
        }
        int totalPage = t.getObject(1);
        String sqlLimit = t.getObject(2);
        Object[] finalParams = t.getObject(3);
        List<R> dataList = queryListForSingleColumn(sqlLimit, finalParams, clz);
        return new Page<R>(dataList, pageNumber, pageSize, totalPage, totalRow);
    }

    public Record queryRecord(String querySql, Object... params) {
        List<Record> records = queryRecords(querySql, params);
        if (records != null && !records.isEmpty()) {
            return records.get(0);
        }
        return null;
    }

    public List<Record> queryRecords(String querySql, Object... params) {
        return queryRecordsFromDB(querySql, params);
    }

    private List<Record> queryRecordsFromDB(String querySqlInput, Object... paramsInput) {
        return callSilence((ds, conn, sql, params) -> {
            ResultSet rs = null;
            PreparedStatement pst = null;
            List<Record> result = new ArrayList<Record>();
            try {
                pst = conn.prepareStatement(sql);
                // 设置参数
                setParameters(pst, params);
                rs = pst.executeQuery();
                ResultSetMetaData rsmetas = rs.getMetaData();
                int columnCount = rsmetas.getColumnCount();
                Record tmp = null;
                while (rs.next()) {
                    tmp = new Record();
                    for (int i = 1, len = columnCount + 1; i < len; ++i) {
                        tmp.put(rsmetas.getColumnLabel(i), i, rs.getObject(i));
                    }
                    result.add(tmp);
                }
                return result;
            } finally {
                DbKit.closeQuietly(rs, pst);
            }
        }, querySqlInput, paramsInput);
    }

    public <T> List<T> queryList(Class<T> modelClass) {
        return queryList(getDialect().getSqlBuilder().buildSimpleQuery(modelClass), null, modelClass);
    }

    @Override
    public <T> T queryFirst(final String querySql, Object[] params, Class<T> modelClass) {
        List<T> dataList = queryList(querySql, params, modelClass);
        return dataList != null && !dataList.isEmpty() ? dataList.get(0) : null;
    }

    public <T> List<T> queryList(final String querySql, final Object[] params, Class<T> modelClass) {
        return queryListFromDB(querySql, params, modelClass);
    }

    private <T> List<T> queryListFromDB(final String querySqlInput, final Object[] paramsInput, Class<T> modelClass) {

        Dialect dialect = getDialect();

        return callSilence((ds, connection, sql, params) -> {
            List<T> result = new ArrayList<>();
            ResultSet rs = null;
            PreparedStatement pst = null;
            ModelFabric mf = ModelCacheUtil.getModelFabric(modelClass);
            Map<String, String> mapping = mf.getJdbcMapping();
            Map<String, Field> fieldMap = mf.getFieldMap();
            Map<String, TypeHandler<?>> typeHandlerMap = mf.getTypeHandlerMap();

            try {
                pst = connection.prepareStatement(sql);
                // 设置参数
                setParameters(pst, params);
                rs = pst.executeQuery();
                ResultSetMetaData rsmetas = rs.getMetaData();
                int columnCount = rsmetas.getColumnCount();
                String[] labelNames = new String[columnCount + 1];
                for (int i = 1; i < labelNames.length; i++) {
                    labelNames[i] = rsmetas.getColumnLabel(i);
                }
                while (rs.next()) {
                    T instance = modelClass.getDeclaredConstructor().newInstance();
                    for (int i = 1, len = columnCount + 1; i < len; ++i) {
                        Field f = fieldMap.get(mapping.get(labelNames[i]));
                        if (f != null) {
                            TypeHandler<?> typeHandler = typeHandlerMap.get(f.getName());
                            Object tmp1 = typeHandler == null ? OrmUtil.getResult(dialect, rs, i, f.getType())
                                    : OrmUtil.getResult(rs, i, typeHandler);
                            if (tmp1 != null) {
                                if (!f.isAccessible()) {
                                    f.setAccessible(true);
                                }
                                f.set(instance, tmp1);
                            }
                        }
                    }
                    result.add(instance);
                }
                return result;
            } catch (Exception e) {
                throw new HORMException(e);
            } finally {
                DbKit.closeQuietly(rs, pst);
            }
        }, querySqlInput, paramsInput);
    }

    public <T> T queryFirstForSingleColumn(ClasspathSqlResource sqlResource, Map<String, Object> params,
                                           Class<T> modelClass) {
        List<T> dataList = queryListForSingleColumn(sqlResource, params, modelClass);
        if (dataList != null && !dataList.isEmpty()) {
            return dataList.get(0);
        }
        return null;
    }

    public <T> List<T> queryListForSingleColumn(ClasspathSqlResource sqlResource, Map<String, Object> params,
                                                Class<T> modelClass) {
        Pair tp = parseSql(sqlResource, params);
        String sql = (String) tp.get(0);
        Object[] paramsArray = (Object[]) tp.get(1);
        return queryListForSingleColumn(sql, paramsArray, modelClass);
    }

    public <T> T queryFirstForSingleColumn(final String querySql, final Object[] params, Class<T> modelClass) {
        List<T> dataList = queryListForSingleColumn(querySql, params, modelClass);
        if (dataList != null && !dataList.isEmpty()) {
            return dataList.get(0);
        }
        return null;
    }

    /**
     * 查询1列
     *
     * @param querySql   查询sql
     * @param params     查询参数
     * @param modelClass 该列对应的类型
     */
    public <T> List<T> queryListForSingleColumn(final String querySql, final Object[] params, Class<T> modelClass) {
        return queryListForSingleColumnFromDB(querySql, params, modelClass);
    }

    private <T> List<T> queryListForSingleColumnFromDB(final String querySqlInput, final Object[] paramsInput,
                                                       Class<T> modelClass) {

        Dialect dialect = getDialect();

        return callSilence((ds, connection, sql, params) -> {
            List<T> result = new ArrayList<>();
            ResultSet rs = null;
            PreparedStatement pst = null;
            try {
                pst = connection.prepareStatement(sql);
                // 设置参数
                setParameters(pst, params);
                rs = pst.executeQuery();
                while (rs.next()) {
                    result.add(OrmUtil.getResult(dialect, rs, 1, modelClass));
                }
                return result;
            } catch (Exception e) {
                throw new HORMException(e);
            } finally {
                DbKit.closeQuietly(rs, pst);
            }
        }, querySqlInput, paramsInput);
    }

    public <T> T queryFirst(ClasspathSqlResource sqlResource, Map<String, Object> params, Class<T> modelClass) {
        List<T> dataList = queryList(sqlResource, params, modelClass);
        if (dataList != null && !dataList.isEmpty()) {
            return dataList.get(0);
        }
        return null;
    }

    public <T> List<T> queryList(ClasspathSqlResource sqlResource, Map<String, Object> params, Class<T> modelClass) {
        Pair tp = parseSql(sqlResource, params);
        String sql = (String) tp.get(0);
        Object[] paramsArray = (Object[]) tp.get(1);
        return queryList(sql, paramsArray, modelClass);
    }

    public Record queryRecord(ClasspathSqlResource sqlResource, Map<String, Object> params) {
        List<Record> records = queryRecords(sqlResource, params);
        if (records != null && !records.isEmpty()) {
            return records.get(0);
        }
        return null;
    }

    public List<Record> queryRecords(ClasspathSqlResource sqlResource, Map<String, Object> params) {
        Pair tp = parseSql(sqlResource, params);
        String sql = (String) tp.get(0);
        Object[] paramsArray = (Object[]) tp.get(1);
        return queryRecords(sql, paramsArray);
    }

    @Override
    public Pair parseSql(ClasspathSqlResource sqlResource, Map<String, Object> params) {
        return sqlParser.parseSql(sqlResource, injectParams(params));
    }

    @Override
    public Pair parseSql(String statement, String type, Map<String, Object> params) {
        return sqlParser.parseSql(statement, type, injectParams(params));
    }

    protected Map<String, Object> injectParams(Map<String, Object> params) {
        if (params == null) {
            params = new HashMap<>(8);
        }
        params.put(InjectParameters.CURRENT_DB_TYPE, getDialect().getDbType().getValue()); // 当前数据库类型
        params.put(InjectParameters.CURRENT_DATASOURCE_NAME, getDataSourceName()); // 当前数据源名称
        return params;
    }

    protected <T> T callSilence(PreparedCallable<T> preparedCallable, String sql, Object[] params) {
        try {
            return super.call(preparedCallable, sql, params);
        } catch (Exception e) {
            GLog.error("OrmClient.callSilence error", e);
            throw new HORMException(e);
        }
    }

    /**
     * 解析为分页sql
     *
     * @return Tuple 0:总行数，1:总页数，2:sql，3:参数
     */
    protected Tuple buildPaginatorSql(String sql, int pageNumber, int pageSize, Object[] params) {
        Tuple t = getDialect().getSqlBuilder().buildPaginatorSql(sql, pageNumber, pageSize);
        String sqlCount = t.getObject(0);
        Record r = queryRecord(sqlCount, params);
        int totalRow = r == null ? 0 : Integer.parseInt(r.get("totalCount").toString());
        if (totalRow == 0) {
            // 空
            return Tuple.apply(0, null, null);
        }
        int totalPage = (int) (totalRow / pageSize);
        if (totalRow % pageSize != 0) {
            totalPage++;
        }
        String sqlLimit = t.getObject(1);
        Object[] pms = t.getObject(2);
        int paramSize = params == null ? 0 : params.length;
        int pageParamSize = pms == null ? 0 : pms.length;
        int finalParamSize = paramSize + pageParamSize;
        Object[] finalParams = new Object[finalParamSize];
        // 查询参数
        if (paramSize > 0) {
            System.arraycopy(params, 0, finalParams, 0, paramSize);
        }
        // 分页参数
        if (pageParamSize > 0) {
            System.arraycopy(pms, 0, finalParams, paramSize, pageParamSize);
        }
        return Tuple.apply(totalRow, totalPage, sqlLimit, finalParams);
    }

    protected void setParameters(PreparedStatement pst, Object... params) throws SQLException {
        if (params != null) {

            Dialect dialect = getDialect();
            for (int i = 0, size = params.length; i < size; i++) {
                OrmUtil.setParameter(dialect, pst, i + 1, params[i]);
            }
        }
    }

    @Override
    public String toString() {
        return "OrmClient [DbType --> " + getDialect().getDbType().getValue() + ", DataSourceName --> "
                + getDataSourceName() + "]";
    }

}